package com.ilink.dataanalysis.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ilink.dataanalysis.entity.VO.MajorInfo;
import com.ilink.ilinkcommon.domain.User;
import org.apache.ibatis.annotations.*;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * 数据大屏基于tb_user表的数据库复杂查询
 */
@Mapper
public interface TbUserMapper extends BaseMapper<User> {

    @Select("SELECT COUNT(DISTINCT school_name) FROM tb_user " +
            "WHERE school_name IS NOT NULL AND school_name <> ''")
    int getDifferentSchoolCount();

    @Select("SELECT COUNT(DISTINCT userMajor) FROM tb_user " +
            "WHERE userMajor IS NOT NULL AND userMajor <> ''")
    int getDifferentMajorCount();

    @Select("SELECT " +
            "SUM(IF(student_status = '本科生' AND enrollment_year = YEAR(CURDATE()), 1, 0)) AS 大一, " +
            "SUM(IF(student_status = '本科生' AND enrollment_year = YEAR(CURDATE()) - 1, 1, 0)) AS 大二, " +
            "SUM(IF(student_status = '本科生' AND enrollment_year = YEAR(CURDATE()) - 2, 1, 0)) AS 大三, " +
            "SUM(IF(student_status = '本科生' AND enrollment_year = YEAR(CURDATE()) - 3, 1, 0)) AS 大四, " +
            "SUM(IF(student_status = '硕士研究生', 1, 0)) AS 硕士研究生, " +
            "SUM(IF(student_status = '博士研究生', 1, 0)) AS 博士研究生 " +
            "FROM tb_user")
    Map<String, BigDecimal> getStudentGradeAndCount();

    @Select("SELECT userMajor, COUNT(*) as count FROM tb_user " +
            "WHERE userMajor IS NOT NULL AND userMajor <> '' " +
            "GROUP BY userMajor")
    @Results(value = {
            @Result(property = "name", column = "userMajor"),
            @Result(property = "value", column = "count")
    })
    List<MajorInfo> getMajorAndCount();

    @Select("SELECT IFNULL(user_counts.user_count, 0) AS user_count " +
            "FROM " +
            "    (SELECT CURDATE() - INTERVAL c.number DAY AS date " +
            "     FROM (SELECT 1 number UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 " +
            "           UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) c " +
            "     ORDER BY number) date_table " +
            "LEFT JOIN " +
            "    (SELECT DATE(create_at) AS date, COUNT(*) AS user_count " +
            "     FROM tb_user " +
            "     WHERE create_at >= CURDATE() - INTERVAL 10 DAY " +
            "     GROUP BY DATE(create_at)) user_counts " +
            "ON date_table.date = user_counts.date " +
            "ORDER BY date_table.date")
    List<Integer> getNewUserOfEveryDay();

    @Select("SELECT school_name, COUNT(*) AS student_count " +
            "FROM tb_user " +
            "WHERE school_name IS NOT NULL AND school_name <> '' "+
            "GROUP BY school_name")
    List<Map<String, Object>> getSchoolAndItsStudent();

    @Select("SELECT COUNT(*) AS user_count " +
            "FROM tb_user " +
            "WHERE create_at < CURDATE() - INTERVAL 10 DAY ")
    int getPreviousUserCount();
}
